#!/usr/bin/perl

#If $ARGV[0] equals 'CREATING' that means that you will be creating tables fresh, otherwise you will be updating existing tables.

use Cwd;

$baseurl = getcwd;


if ($ARGV[0] eq 'CREATING') {
    $mode = 'CREATING';
} else {
    $mode = 'UPDATING';
}

if ($ARGV[1] =~ /\//) {
    ($baseurl = $ARGV[1]) =~ s/[\/]*$//;
}

$error_outs = $baseurl . '/errors_Auths.txt';
$print_outs = $baseurl . '/prints_Auths.txt';

binmode(STDOUT, ':utf8');

open (STDERR, ">$error_outs");
open (STDOUT, ">$print_outs");

print "baseurl: " . $baseurl . "\n";

# local system variables
$connect = $baseurl . '/connect.pl';
require $connect;

use DBI;

use IO::File;

use Data::Dumper;

use Roman; #for roman numerals

use utf8;

use Unicode::Normalize;

use MARC::Charset 'marc8_to_utf8';

use MARC::Batch;

#use strict;
#  use Carp ();
#    local $SIG{__WARN__} = \&Carp::cluck;

&hashes_and_arrays; #SUBROUTINE AT END OF SCRIPT

if ($ARGV[0] eq 'CREATING') {
$next_local_control_number = 1000001;
}

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_time = "$HOUR:$MIN:$SEC";

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { mysql_enable_utf8 => 1 }) or die "couldn't connect to database: " . DBI->errstr;

$folder_name = 'authorities';

$where = $baseurl . '/' . $folder_name;

while (defined($next_file = <$where/*.out>)) {

    ($batch_textfiles = $next_file) =~ s/authorities/authorities_textfiles/g;
    ($tracings_batch_textfiles = $next_file) =~ s/authorities/authorities_tracings_textfiles/g;

$batch_loader = IO::File->new("> $batch_textfiles") || die "cannot open $batch_textfiles for writing: $!\n";
binmode( $batch_loader, ':utf8' );
flock($batch_loader, 2);

$tracings_batch_loader = IO::File->new("> $tracings_batch_textfiles") || die "cannot open $tracings_batch_textfiles for writing: $!\n";
binmode( $tracings_batch_loader, ':utf8' );
flock($tracings_batch_loader, 2);

$edit1 = "perl -pi -e 's/\n|\t/\x1a/g' $next_file";
system($edit1);

print "$next_file\n";

    open(IN, "< $next_file") || die "cannot open $next_file for reading: $!";

            while(<IN>) {


$batch = MARC::Batch->new( 'USMARC', $next_file );
$batch->strict_off();
$batch->warnings_off();

while ($record = $batch->next()) {

%query_insert = (
  source_record_type => 'MARC',
  source_record => '',
  local_control_number => '',
  record_owner => $institution,
  owner_level => '4',
  universal_identifier => '',
  national_control_number => '', 
  geo_subdivide => '',
  name_use => '',
  subj_use => '',
  series_use => '',
  mainheading => '',
  index_name => '',
  heading_raw => '',
  authors_personal_invalid => '',
  authors_corporate_invalid => '',
  authors_conferences_invalid => '',
  subjects_personal_invalid => '',
  subjects_corporate_invalid => '',
  subjects_conferences_invalid => '',
  subjects_titles_invalid => '',
  subjects_chronological_invalid => '',
  subjects_topical_invalid => '',
  subjects_geographic_invalid => '',
  subjects_genre_invalid => '',
  titles_uniform_invalid => '',
  authors_personal_broader => '',
  authors_corporate_broader => '',
  authors_conferences_broader => '',
  subjects_personal_broader => '',
  subjects_corporate_broader => '',
  subjects_conferences_broader => '',
  subjects_titles_broader => '',
  subjects_chronological_broader => '',
  subjects_topical_broader => '',
  subjects_geographic_broader => '',
  subjects_genre_broader => '',
  titles_uniform_broader => '',
  authors_personal_related => '',
  authors_corporate_related => '',
  authors_conferences_related => '',
  subjects_personal_related => '',
  subjects_corporate_related => '',
  subjects_conferences_related => '',
  subjects_titles_related => '',
  subjects_chronological_related => '',
  subjects_topical_related => '',
  subjects_geographic_related => '',
  subjects_genre_related => '',
  titles_uniform_related => '',
  isn => '',
  oclc_arn => '',
  worktitles => '',
  languages => '',
  auth_status => '',
  created_date => '', 
  updated_date => '', 
  source_data => '',
  source_nondata => '',
  notes_public => '',
  notes_nonpublic => '',
  urls => '',
  lc_callnumber => '',
  lc_class => '',
  lcc_range => '',
  lc_class_label => '',
  sudoc_callnumber => '',
  sudoc_class => '',
  dewey_callnumber => '',
  dewey_class => '',
  other_callnumber => '',
  other_class => '',
  local_callnumber => '',
  local_class => '',
  call_numbers => '',
  unaccounted_marc_fields => '',
  marc_errors => ''
);

$this_local_control_number = substr($record->field('907')->subfield('a'), 2, 7);

if ($ARGV[0] eq 'CREATING') {
##THIS STARTS WHERE THE DELETED AUTH RECORDS ARE LOADED

#print "$next_local_control_number < $this_local_control_number\n";
    while($next_local_control_number < $this_local_control_number) {

        $query_insert{'local_control_number'} = $institution . 'a' . $next_local_control_number;
        $query_insert{'auth_status'} = 'x';

        @keys = keys %query_insert;
        @values = values %query_insert;

        $mysql_keys = join(", ", @keys);
        $mysql_values = join("\t", @values);
        $mysql_values =~ s/\\\t/\\ \t/g;

        print $batch_loader $mysql_values . "\n";

        $next_local_control_number++;

    }

    $next_local_control_number++;

##THIS ENDS WHERE THE DELETED AUTH RECORDS ARE LOADED
}

$field_780_indicator2_4 = 0;
$field_785_indicator2_6 = 0;
$field_785_indicator2_7 = 0;

$this_local_control_number = $institution . 'a' . $this_local_control_number;
$query_insert{'universal_identifier'} = $this_local_control_number . "_";

$query_insert{'source_record'} = $record->as_usmarc();

if($query_insert{'source_record'} =~ /\x1a/) {
    print $this_local_control_number . " has an escape character (either a tab or line ending) in a field marked with \x1a \n";
    uniqueness('marc_errors', '', $this_local_control_number . " has an escape character (either a tab or line ending) in a field marked with \x1a");
}

if ($consortium ne '') {
    if(grep /$institution/, @MeSH_major_players) {
        $query_insert{'owner_level'} = '0';
    } elsif(grep /$institution/, @major_players) {
        $query_insert{'owner_level'} = '1';
    } elsif(grep /$institution/, @consortium_members) {
        $query_insert{'owner_level'} = '2';
    }
} else {
    $query_insert{'owner_level'} = '1';
}


foreach $fLDR ($record->leader()) {

}

foreach $f001 ($record->field('001|035')) {
    $query_insert{'oclc_arn'} = $f001->as_string;
        $query_insert{'universal_identifier'} = $query_insert{'oclc_arn'};

            ($temp_national_control_number = $f001->as_string) =~ s/\s//g;
            $query_insert{'national_control_number'} = $temp_national_control_number;

}

foreach $f008 ($record->field('008')) {
    $f008_string = $record->field('008')->as_string;
    $query_insert{'geo_subdivide'} = substr($f008_string, 6, 1);
    $query_insert{'name_use'} = substr($f008_string, 14, 1);
    $query_insert{'subj_use'} = substr($f008_string, 15, 1);
    $query_insert{'series_use'} = substr($f008_string, 16, 1);
}

foreach $f01X ($record->field('01[06]')) {
    $indicator1 = $f01X->indicator(1);
    $indicator2 = $f01X->indicator(2);
    $this_tag = $f01X->tag;
    $this_label = '';

    if($this_tag =~ /010/) {
        $hash_tag = 'national_control_number';
        $codes = '[az8]';
        $this_label = 'LCCN: ';
    } elsif($this_tag =~ /016/) {
        $hash_tag = 'national_control_number';
        $codes = '[az28]'; 
        if($indicator2 =~ /7/) {
            $this_label = $f01X->subfield('2') . ': ';
        } elsif($indicator2 =~ /[ ]/) {
            $this_label = 'LAC: ';
        }
    }

    foreach $f01X_subfields ($f01X->subfields()) {
        ($f01Xcode, $f01Xdata ) = @$f01X_subfields;
        if($f01Xcode =~ /[z]/) {
            $this_label = 'Invalid ' . $this_label;
        }

        if($f01Xcode =~ /[a]/) {
            $f01Xdata =~ s/\s//g;
            $query_insert{'national_control_number'} = $f01Xdata;
#            uniqueness('national_control_number', $this_label . '\\t', $f01Xdata);
        }

    uniqueness_diacritics('notes_public', "$this_label ", $f01Xdata);

}

}

foreach $f02X ($record->field('02[024]')) {
    $indicator1 = $f02X->indicator(1);
    $indicator2 = $f02X->indicator(2);
    $this_tag = $f02X->tag;
    $this_label = '';
    $this_isn = '';
    $this_note = '';

    if($this_tag =~ /020/) {
        $hash_tag = 'isn';
        $codes = '[acz68]';
        $this_label = 'ISBN: ';
    } elsif($this_tag =~ /022/) {
        $hash_tag = 'isn';
        $codes = '[almyz68]';
        $this_label = 'ISSN: ';
    } elsif($this_tag =~ /024/) {
        $hash_tag = 'isn';
        $codes = '[acdz268]';
    }

    if($this_tag =~ /024/) {
        if($indicator1 =~ /7/) {
            $this_label = $f02X->subfield('2') . ': '; #Source specified in subfield 2
        } elsif($indicator1 =~ /8/) {
            $this_label = ''; #Unspecified type of standard number or code
        }
    }

    foreach $f02X_subfields ($f02X->subfields()) {
    ($f02Xcode, $f02Xdata ) = @$f02X_subfields;
            if($f02Xcode =~ /[aldmyz]/) {
                if($f02Xdata=~ /([0-9xX]{13}|[0-9xX]{10}|[0-9xX]{4}-[0-9xX]{4}).*/) {
                    $this_isn .= $1;
                } else {
                    $this_isn .= $f02Xdata;
                }
           
                if($f02Xcode =~ /[a]/) {
                    uniqueness('isn', $this_label . '\\t', $this_isn);

                    $this_note .= $f02Xdata;
                } elsif($f02Xcode =~ /[mz]/) {
                    uniqueness_diacritics('notes_public', 'Canceled ISN: ', $f02Xdata);
                } elsif($f02Xcode =~ /[y]/) {
                    uniqueness_diacritics('notes_public', 'Invalid ISN: ', $f02Xdata);
                }
            } else {
                $this_note .= $f02Xdata;
            }
    }

    uniqueness_diacritics('notes_public', "$this_label ", $this_note);

}

foreach $f031 ($record->field('031')) {
    #Musical Incipits Information
}

foreach $f034 ($record->field('034')) {
    #Coded Cartographic Mathematical Data
}

foreach $f040 ($record->field('040')) {
    foreach $f040_subfields ($f040->subfields()) {
        ($f040code, $f040data ) = @$f040_subfields;
        if($f040code =~ /f/) {
            $thesaurus = $f040data;
        }
    }

}


foreach $f042 ($record->field('042')) {
    #Authentication Code
}

#foreach $f043 ($record->field('043')) {
#    foreach $f043_subfields ($f043->subfields()) {
#        ($f043code, $f043data ) = @$f043_subfields;
#        if($f043code =~ /a/) {
#            $f043data =~ s/[-]*$//;
#            $this_geographic = $geographics_hash{$f043data};
#            if($this_geographic ne '') {
#                uniqueness('geographics_facet', 'LCSH\\t', $this_geographic);
#            }
#        }
#    }
#}

#foreach $f045 ($record->field('045')) {
#    $indicator1 = $f045->indicator(1);
#    $indicator2 = $f045->indicator(2);
#    $hash_tag = 'coverage_dates_facet';
#
#    foreach $f045_subfields ($f045->subfields()) {
#        ($f045code, $f045data ) = @$f045_subfields;
#        if($f045code =~ /a/) {
#                if(substr($f045data, 0, 1) =~ /[a-d]/) {
#                    $era1 = ' BCE';
#                }
#                if(substr($f045data, 2, 1) =~ /[a-d]/) {
#                    $era2 = ' BCE';
#                } else {
#                    $era2 = '';
#                    if(substr($f045data, 0, 1) =~ /[a-d]/) {
#                        $era2 = ' CE';
#                    }
#                }
#            dates_expansion($hash_tag, 'GENERIC', substr($coverage_dates_hash{substr($f045data, 0, 2)}, 0, 4) . $era1 . '-' . substr($coverage_dates_hash{substr($f045data, 2, 2)}, 5, 4) . $era2);
#        } elsif($f045code =~ /[bc]/) {
#            $this_epoch = substr($f045data, 0, 1);
#            if($this_epoch =~ /c/) {
#                $era = ' BCE';
#            }
#            $this_coverage_date = substr($f045data, 1);
#
#            if($indicator1 =~ /2/) {
#                if($this_date_range ne '') {
#                    $this_date_range .= '-';
#                }
#                $this_date_range .= $this_coverage_date . $era;
#            } else {
#                uniqueness($hash_tag, 'GENERIC\\t', $this_coverage_date . $era . '-' . $this_coverage_date . $era);
#                dates_expansion($hash_tag, 'GENERIC', $this_coverage_date . $era . '-' . $this_coverage_date . $era);
#            }
#        }
#    }
#
#    if($indicator1 =~ /2/) {
#        uniqueness($hash_tag, 'GENERIC\\t', $this_date_range);
#        dates_expansion($hash_tag, 'GENERIC', $this_date_range);
#        undef($this_date_range);
#    }
#}

foreach $f05X ($record->field('050|060|070|086')) {
    $indicator1 = $f05X->indicator(1);
    $indicator2 = $f05X->indicator(2);
    $this_tag = $f05X->tag;
    $this_hash = '';

    if($this_tag =~ /050/) {
        $hash_tag = 'lc_callnumber';
        $class = 'lc_class';
        $codes = '[abd568]';
        $label = 'LCC';
    } elsif($this_tag =~ /060/) { #NLM
        $hash_tag = 'other_callnumber';
        $class = 'other_class';
        $codes = '[abd568]';
        $label = 'NLM';
    } elsif($this_tag =~ /070/) { #NAL
        $hash_tag = 'other_callnumber';
        $class = 'other_class';
        $codes = '[abd68]';
        $label = 'NAL';
    } elsif($this_tag =~ /082/) {
        $hash_tag = 'dewey_callnumber';
        $class = 'dewey_class';
        $codes = '[abd2568]';
        $label = 'DEWEY';
    } elsif($this_tag =~ /086/) {
        $hash_tag = 'sudoc_callnumber';
        $class = 'sudoc_class';
        $codes = '[adz2568]';
        $label = 'SUDOC';
    }

    if($this_tag !~ /050/) {
    uniqueness_diacritics($hash_tag, '', $f05X->as_string);
    uniqueness_diacritics('call_numbers', $label, $f05X->as_string);
    }
    
    if($f05X->as_string =~ /^[a-zA-Z]/) {
        ($this_class) = ($f05X->as_string =~ /(^[a-zA-Z]*)/);
        for($c = 1; $c <= length($this_class); $c++) {
            uniqueness_diacritics($class, '', substr($this_class, 0, $c));
        }
    } else {
        ($this_class) = ($f05X->as_string =~ /(^[0-9]{1})/);
        uniqueness_diacritics($class, '', $this_class . '00');
    }

    if($this_tag =~ /050|090/) {

        if($f05X->subfield('a') =~ /(^[a-zA-Z]*)([0-9]*)([0-9a-zA-Z. ]*)/) {
            $this_class = $1 . (' ' x (3 - length($1)));
            $lc_numeric = (' ' x (4 - length($2))) . $2;
            $decimal_points = $3;

            if($decimal_points =~ /(\.)*([0-9]*)(\.)*([a-zA-Z0-9]*)/) {
                $decimal_point = $2;
                $decimal_point0 = $4;
                if($decimal_point0 =~ /([a-zA-Z]*)([0-9]*)/) {
                    $decimal_alpha = $1;
                    $decimal_point2 = $2;
                }
            } else {
                $decimal_point = substr($decimal_points, 0, 5);
                $decimal_alpha = '';
                $decimal_point2 = '';
            }

            $decimal_point .= ' ' x (5 - length($decimal_point));
            $decimal_alpha .= ' ' x (1 - length($decimal_alpha));
            $decimal_point2 .= ' ' x (6 - length($decimal_point2));

        }

        if($f05X->subfield('b')) {
            $cutter = $f05X->subfield('b');
        }

        $lc_callnumber = $this_class . $lc_numeric . "." . $decimal_point . "." . $decimal_alpha . $decimal_point2 . $cutter;
        $lc_callnumber =~ s/\. / /g;
        uniqueness_diacritics($hash_tag, '', $lc_callnumber);
        uniqueness_diacritics('call_numbers', $label, $lc_callnumber);

        $lcc_class = $this_class . $lc_numeric . $decimal_point . $decimal_alpha . $decimal_point2;
        $lcc_class =~ s/ /0/g;

        @lccclass = split(//, $lcc_class);
        $lcc_range = $alphanumarray{$lccclass[0]} . $alphanumarray{$lccclass[1]} . $alphanumarray{$lccclass[2]} . $lccclass[3] . $lccclass[4] . $lccclass[5] . $lccclass[6] . $lccclass[7] . $lccclass[8] . $lccclass[9] . $lccclass[10] . $lccclass[11] . $alphanumarray{$lccclass[12]} . $lccclass[13] . $lccclass[14] . $lccclass[15] . $lccclass[16] . $lccclass[17] . $lccclass[18];
        uniqueness_diacritics('lcc_range', '', $lcc_range);

#print $lc_callnumber . "\n" . $lcc_class . "\n" . $lcc_range . "\n\n";


        $lcc_group = substr($lcc_range, 0, 2);
        $comparison_lcc_range = substr($lcc_range, 0, 10) . "." . substr($lcc_range, 10);
        while ($c <= $#{$alllcclasses_array[$lcc_group]} ) {
            if($alllcclasses_array[$lcc_group][$c][1] <= $comparison_lcc_range) {
                if($alllcclasses_array[$lcc_group][$c][2] >= $comparison_lcc_range) {
                 push(@these_lc_class_labels, $alllcclasses_array[$lcc_group][$c][0]);
                }
            } else {
                $c = $#{$alllcclasses_array[$lcc_group]};
            }
            $c++;
        }
        $this_lc_class_label = join("\\n", @these_lc_class_labels);
        $query_insert{'lc_class_label'} = $this_lc_class_label;
        undef(@these_lc_class_labels);
    }

}

foreach $f052 ($record->field('052')) {
    #Geographic Classification
}

foreach $f053 ($record->field('053')) {
    #LC Classification Number
}

foreach $f055 ($record->field('055')) {
    #Library and Archives Canada Call Number
}

foreach $f065 ($record->field('065')) {
    #Other Classification Number
}

foreach $f066 ($record->field('066')) {
    #Character Sets Present
}

foreach $f072 ($record->field('072')) {
    #Subject Category Code
}

foreach $f073 ($record->field('073')) {
    #Subdivision Usage
}

foreach $f083 ($record->field('083')) {
    #Dewey Decimal Classificaton Number
}

foreach $f087 ($record->field('087')) {
    #Government Document Classification Number
}

foreach $f1XX ($record->field('1..')) {
    $this_tag = $f1XX->tag();
    $indicator1 = $f1XX->indicator(1);
    $indicator2 = $f1XX->indicator(2);

    $this_hash = '';

    $heading_raw = '';

    $mainheading = '';

    if($this_tag =~ /100/) {
        $index_name_1 = 'authors_';
        $index_name_2 = 'personal';
        $codes = '[a-dghj-tvx-z68]';
        $relators = '[e]';
    } elsif($this_tag =~ /110/) {
        $index_name_1 = 'authors_';
        $index_name_2 = 'corporate';
        $codes = '[a-df-hk-tvx-z68]';
        $relators = '[e]';
    } elsif($this_tag =~ /111/) {
        $index_name_1 = 'authors_';
        $index_name_2 = 'conferences';
        $codes = '[ac-hklnpqstv-z68]';
        $relators = '[j]';
    } elsif($this_tag =~ /130/) {
        if($query_insert{'series_use'} =~ /a/) {
            $index_name_1 = 'series_';
            $index_name_2 = 'traced';
        } else {
            $index_name_1 = 'titles_';
            $index_name_2 = 'uniform';
        }
        $codes = '[adf-ik-pr-tvx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /148/) {
        $index_name_1 = 'subjects_';
        $index_name_2 = 'chronological';
        $codes = '[avx-z]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /150/) {
        $index_name_1 = 'subjects_';
        $index_name_2 = 'topical';
        $codes = '[abvx-z]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /151/) {
        $index_name_1 = 'subjects_';
        $index_name_2 = 'geographic';
        $codes = '[avx-z]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /155/) {
        $index_name_1 = 'subjects_';
        $index_name_2 = 'genre';
        $codes = '[avx-z]';
        $linkage = '[6]';
        $field_link = '[8]';
    }

if($this_tag =~ /100|110|111|148/) {

    foreach $f1XX_subfields ($f1XX->subfields()) {
    ($f1XXcode, $f1XXdata ) = @$f1XX_subfields;

    $heading_raw .= '|' . $f1XXcode . $f1XXdata;

        if($f1XXcode =~ /$codes/) {
            if($f1XXcode =~ /a/) {
                $this_hash .= $f1XXdata;
            } elsif($f1XXcode =~ /[fmors]/) {
                #DATE OF WORK -- NOT NEEDED FOR worktitles
                #METHOD OF PERFORMANCE FOR MUSIC -- NOT NEEDED FOR worktitles
                #ARRANGED STATEMENT FOR MUSIC -- NOT NEEDED FOR worktitles
                #KEY FOR MUSIC -- NOT NEEDED FOR worktitles
                #VERSION OF WORK -- NOT NEEDED FOR worktitles
            } elsif($f1XXcode =~ /k/) {
                #FORM SUBHEADING
                $this_hash .= $f1XXdata;
            } elsif($f1XXcode =~ /l/) {
                #LANGUAGE OF WORK -- NOT NEEDED FOR worktitles
                $f1XXdata =~ s/(^ |[\.,:;\/]$| $)//g;
                uniqueness_diacritics('languages', '', $f1XXdata);
            } elsif($f1XXcode =~ /t/) {
                $f1XXdata = ucfirst($f1XXdata);
                $this_hash .= '\t' . $f1XXdata;
                $index_name_2 = 'titles';
            } elsif($f1XXcode =~ /[vxyz]/) {
                #SUBDIVISIONS -- NOT NEEDED FOR worktitles
                $index_name_1 = 'subjects_';
            } else {
                $this_hash .= ' ' . $f1XXdata;
            }
        }
    }

    if($this_hash =~ /\\t/) {
        $this_hash =~ s/([\.,:;\/]|[\.,:;\/] | )$//g;
        $this_hash =~ s/\.[ ]*\\t/\\t/g;
        uniqueness_diacritics('worktitles', '', $this_hash);
    }
}

if($this_tag =~ /130/) {

    foreach $f1XX_subfields ($f1XX->subfields()) {
    ($f1XXcode, $f1XXdata ) = @$f1XX_subfields;

    $heading_raw .= '|' . $f1XXcode . $f1XXdata;

            if($f1XXcode =~ /a/) {
                $this_hash .= $f1XXdata;
            } elsif($f1XXcode =~ /[fmors]/) {
                #DATE OF WORK -- NOT NEEDED FOR worktitles
                #METHOD OF PERFORMANCE FOR MUSIC -- NOT NEEDED FOR worktitles
                #ARRANGED STATEMENT FOR MUSIC -- NOT NEEDED FOR worktitles
                #KEY FOR MUSIC -- NOT NEEDED FOR worktitles
                #VERSION OF WORK -- NOT NEEDED FOR worktitles
            } elsif($f1XXcode =~ /l/) {
                $f1XXdata =~ s/(^ |[\.,:;\/]$| $)//g;
                uniqueness_diacritics('languages', '', $f1XXdata);
            } elsif($f1XXcode =~ /[vxyz]/) {
                #SUBDIVISIONS -- NOT NEEDED FOR worktitles
                $index_name_1 = 'subjects_';
                $index_name_2 = 'titles';
            } else {
                $this_hash .= ' ' . $f1XXdata;
            }
    }
    
    $this_hash =~ s/([\.,:;\/]|[\.,:;\/] | )$//g;
    uniqueness_diacritics('worktitles', '', '\t'. $this_hash);
}

if($this_tag =~ /15[015]/) {

    foreach $f1XX_subfields ($f1XX->subfields()) {
        ($f1XXcode, $f1XXdata ) = @$f1XX_subfields;

    $heading_raw .= '|' . $f1XXcode . $f1XXdata;

        if($f1XXcode =~ /$codes/) {
            if($f1XXcode =~ /[a]/) {
                $this_hash .= $f1XXdata;
                $f1XXdata =~ s/\.$//;

                if($this_tag =~ /150/) {
                    if($f1XXdata =~ /^($cardinals_keys)(?: |-)($cardinals_keys)(?: |-)($cardinals_keys)(?: $|$|, A.D)/i) {
                        $query_insert{'index_name'} = 'subjects_chronological';
                    } elsif($f1XXdata =~ /^($cardinals_keys)( |-o-|-)($cardinals_keys)(?: $|$|, A.D)/i) {
                        $query_insert{'index_name'} = 'subjects_chronological';
                    } elsif($f1XXdata =~ /^($cardinals_keys)(?: |-)($places_keys)(?: $|$|, A.D)/i) {
                        $query_insert{'index_name'} = 'subjects_chronological';
                    } elsif($f1XXdata =~ /^($cardinals_keys)(?: |-)($decades_keys)/i) {
                        $query_insert{'index_name'} = 'subjects_chronological';
                    } elsif($f1XXdata =~ /^($cardinals_keys)(?: |-)($ordinals_keys) century/i) {
                        $query_insert{'index_name'} = 'subjects_chronological';
                    } elsif($f1XXdata =~ /^($ordinals_keys) century/i) {
                        $query_insert{'index_name'} = 'subjects_chronological';
                    }
                }
            } elsif($f1XXcode =~ /[vxy]/) {
                $this_hash .= '--' . $f1XXdata;
            } elsif($f1XXcode =~ /[z]/) {
                $this_hash .= '--' . $f1XXdata;
                $f1XXdata =~ s/\.$//;
                push(@this_subfield_Zs, $f1XXdata);
            } else {
                $this_hash .= ' ' . $f1XXdata;
            }
        } elsif($f1XXcode =~ /$subparts/) {
            $this_hash .= ' ' . $f1XXdata;
        } elsif($f1XXcode =~ /[7]/) {
            $thesaurus = $f1XXdata;
        } else {
            $this_hash .= ' ' . $f1XXdata;
        }
    }

#    if($#this_subfield_Zs == 2) {
#        uniqueness_diacritics('geographics_facet', $thesaurus, $this_subfield_Zs[1] . ' (' . $this_subfield_Zs[2] . ' : ' . $this_subfield_Zs[0] . ') ');
#    } elsif($#this_subfield_Zs == 1) {
#        uniqueness_diacritics('geographics_facet', $thesaurus, $this_subfield_Zs[1] . ' (' . $this_subfield_Zs[0] . ')');
#    } elsif($#this_subfield_Zs == 0) {
#        uniqueness_diacritics('geographics_facet', $thesaurus, $this_subfield_Zs[0]);
#    } elsif($#this_subfield_Zs > 2 ) {
#        foreach $t ($this_subfield_Zs) {
#            $too_many_geographics .= ' ' . $t;
#        }
#        uniqueness_diacritics('geographics_facet', $thesaurus, $too_many_geographics);
#    }
#    undef(@this_subfield_Zs);
#
#    uniqueness_diacritics($hash_tag, $thesaurus, $this_hash);

}

#foreach $f18X ($record->field('18[015]')) {
#    foreach $f18X_subfields ($f18X->subfields()) {
#    ($f18Xcode, $f18Xdata ) = @$f18X_subfields;
#
#    $heading_raw .= '|' . $f18Xcode . $f18Xdata;
#
#    }
#}

$heading_raw =~ s/[ ]*\.\|/|/g;
$mainheading = $heading_raw;
$mainheading =~ s/\|[[aA]//g;
$mainheading =~ s/\|t/\\t/g;
$mainheading =~ s/\|[[0-9b-suvB-SUV]/ /g;
$mainheading =~ s/\|[[x-z]/--/g;

uniqueness_diacritics('mainheading', '', $mainheading);

uniqueness_diacritics('heading_raw', '', $heading_raw);

$query_insert{'index_name'} = $index_name_1 . $index_name_2;

#print $tracings_batch_loader $this_local_control_number . '\t' . $query_insert{'record_owner'} . '\t' . $heading_raw  . '\t' .  $index_name . '\t' . $mainheading . '\t' . $query_insert{'national_control_number'} . '\t' . $query_insert{'oclc_arn'} . '\t' . "\n";

} #END: foreach $f1XX 

foreach $f2XX ($record->field('260|360')) {
    $this_tag = $f2XX->tag();
    $indicator1 = $f2XX->indicator(1);
    $indicator2 = $f2XX->indicator(2);

    #COMPLEX REFERENCE NOTES, MAY BE ABLE TO GENERATE BROADER TERMS

    $this_label = '';
    $this_hash = '';

    if($this_tag =~ /260]/) {
        $this_label = 'Complex See Reference';
        $codes = '[az68]';
    } elsif($this_tag =~ /360/) {
        $this_label = 'Complex See Also Reference';
        $codes = '[az68]';
    }

        if($f2XXcode =~ /$codes/) {
            if($f2XXcode =~ /a/) {
                $this_hash = $f2XXdata;
                if($this_tag =~ /644/) {
                    if($f2XXdata =~ /f/) {
                        $this_hash = 'Analyzed in full';
                    } elsif($f2XXdata =~ /p/) {
                        $this_hash = 'Analyzed in part';
                    } elsif($f2XXdata =~ /n/) {
                        $this_hash = 'Not analyzed';
                    }
                } elsif($this_tag =~ /645/) {
                    if($f2XXdata =~ /t/) {
                        $this_hash = 'Traced as a series added entry';
                    } elsif($f2XXdata =~ /n/) {
                        $this_hash = 'Not traced as a series added entry';
                    }
                } elsif($this_tag =~ /646/) {
                    if($f2XXdata =~ /s/) {
                        $this_hash = 'Volumes are classified separately';
                    } elsif($f2XXdata =~ /c/) {
                        $this_hash = 'Volumes are classified as a collection';
                    } elsif($f2XXdata =~ /m/) {
                        $this_hash = 'Volumes are classified with main or other series';
                    }
                }

            } elsif($f2XXcode =~ /[bdz]/) {
                $this_hash = ' ' . $f2XXdata;
            }
        }

    uniqueness_diacritics('notes_public', '', $f2XX->as_string);

} #END: foreach $f2XX


foreach $f4XX ($record->field('4..')) {
    $this_tag = $f4XX->tag();
    $indicator1 = $f4XX->indicator(1);
    $indicator2 = $f4XX->indicator(2);

    $this_hash = '';

    $heading_raw = '';

    if($this_tag =~ /400/) {
        $hash_tag = 'authors_personal_invalid';
        $codes = '[a-dghj-tvx-z68]';
        $relators = '[e]';
    } elsif($this_tag =~ /410/) {
        $hash_tag = 'authors_corporate_invalid';
        $codes = '[a-df-hk-tvx-z68]';
        $relators = '[e]';
    } elsif($this_tag =~ /411/) {
        $hash_tag = 'authors_conferences_invalid';
        $codes = '[ac-hklnpqstv-z68]';
        $relators = '[j]';
    } elsif($this_tag =~ /430/) {
        $hash_tag = 'titles_uniform_invalid';
        $codes = '[adf-ik-pr-tvx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /448/) {
        $hash_tag = 'subjects_chronological_invalid';
        $codes = '[aivx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /450/) {
        $hash_tag = 'subjects_topical_invalid';
        $codes = '[abvx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /451/) {
        $hash_tag = 'subjects_geographic_invalid';
        $codes = '[abvx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /455/) {
        $hash_tag = 'subjects_genre_invalid';
        $codes = '[abvx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    }

    $f4XXw = $record->field('4..')->subfield('w');

        if(substr($f4XXw, 0, 1) =~ /a/) {
            $this_label = 'Earlier heading: ';
        } elsif(substr($f4XXw, 0, 1) =~ /b/) {
            $this_label = 'Later heading: ';
        } elsif(substr($f4XXw, 0, 1) =~ /d/) {
            $this_label = 'Acronym: ';
        } elsif(substr($f4XXw, 0, 1) =~ /f/) {
            $this_label = 'Musical composition: ';
        } elsif(substr($f4XXw, 0, 1) =~ /g/) {
            $this_label = 'Broader term: ';
        } elsif(substr($f4XXw, 0, 1) =~ /h/) {
            $this_label = 'Narrower term: ';
        } elsif(substr($f4XXw, 0, 1) =~ /i/) {
            $this_label = ''; #Reference instruction phrase in subfield $i
        } elsif(substr($f4XXw, 0, 1) =~ /t/) {
            $this_label = 'Immediate parent body: ';
        } else {
            $this_label = '';
        }


if($this_tag =~ /400|410|411|448/) {

    foreach $f4XX_subfields ($f4XX->subfields()) {
    ($f4XXcode, $f4XXdata ) = @$f4XX_subfields;

#GET WORK TITLE WORKED IN HERE

    if($f4XXcode !~ /w/) {
        $heading_raw .= '|' . $f4XXcode . $f4XXdata;
    }

        if($f4XXcode =~ /$codes/) {
            if($f4XXcode =~ /a/) {
                $this_hash = $f4XXdata;
            } elsif($f4XXcode =~ /i/) {
                $this_label = $f4XXdata;
            } elsif($f4XXcode =~ /t/) {
                $this_hash .= '\t' . $f4XXdata;
            } else {
                $this_hash .= ' ' . $f4XXdata;
            }
        }

        if($this_hash =~ /\\t/) {
            $hash_tag = 'titles_uniform_invalid';
            $this_hash =~ s/([\.,:;\/]|[\.,:;\/] | )$//g;
            $this_hash =~ s/\.[ ]*\\t/\\t/g;
        }

    }

#    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

}

if($this_tag =~ /430/) {

    foreach $f4XX_subfields ($f4XX->subfields()) {
    ($f4XXcode, $f4XXdata ) = @$f4XX_subfields;

    if($f4XXcode !~ /w/) {
        $heading_raw .= '|' . $f4XXcode . $f4XXdata;
    }

            if($f4XXcode =~ /a/) {
                $this_hash .= $f4XXdata;
            } elsif($f4XXcode =~ /f/) {
                #DATE OF WORK -- NOT NEEDED FOR worktitles
            } elsif($f4XXcode =~ /l/) {
                $f4XXdata =~ s/(^ |[\.,:;\/]$| $)//g;
                uniqueness_diacritics('languages', '', $f4XXdata);
            } elsif($f4XXcode =~ /m/) {
                $this_hash .= ' ' . $f4XXdata;
            } elsif($f4XXcode =~ /[vxyz]/) {
                $this_hash .= '--' . $f4XXdata;
            } else {
                $this_hash .= ' ' . $f4XXdata;
            }
    }

#    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

}

if($this_tag =~ /45[015]/) {

    foreach $f4XX_subfields ($f4XX->subfields()) {
        ($f4XXcode, $f4XXdata ) = @$f4XX_subfields;

    if($f4XXcode !~ /w/) {
        $heading_raw .= '|' . $f4XXcode . $f4XXdata;
    }

        if($f4XXcode =~ /$codes/) {
            if($f4XXcode =~ /[a]/) {
                $this_hash = $f4XXdata;
                $f4XXdata =~ s/\.$//;

                if($this_tag =~ /450/) {
                    if($f4XXdata =~ /^($cardinals_keys)(?: |-)($cardinals_keys)(?: |-)($cardinals_keys)(?: $|$|, A.D)/i) {
                        $hash_tag = 'subjects_chronological_invalid';
                    } elsif($f4XXdata =~ /^($cardinals_keys)( |-o-|-)($cardinals_keys)(?: $|$|, A.D)/i) {
                        $hash_tag = 'subjects_chronological_invalid';
                    } elsif($f4XXdata =~ /^($cardinals_keys)(?: |-)($places_keys)(?: $|$|, A.D)/i) {
                        $hash_tag = 'subjects_chronological_invalid';
                    } elsif($f4XXdata =~ /^($cardinals_keys)(?: |-)($decades_keys)/i) {
                        $hash_tag = 'subjects_chronological_invalid';
                    } elsif($f4XXdata =~ /^($cardinals_keys)(?: |-)($ordinals_keys) century/i) {
                        $hash_tag = 'subjects_chronological_invalid';
                    } elsif($f4XXdata =~ /^($ordinals_keys) century/i) {
                        $hash_tag = 'subjects_chronological_invalid';
                    }
                }
            } elsif($f4XXcode =~ /i/) {
                $this_label = $f4XXdata;            
            } elsif($f4XXcode =~ /[vxy]/) {
                $this_hash .= '--' . $f4XXdata;
            } elsif($f4XXcode =~ /[z]/) {
                $this_hash .= '--' . $f4XXdata;
                $f4XXdata =~ s/\.$//;
                push(@this_subfield_Zs, $f4XXdata);
            } else {
                $this_hash .= ' ' . $f4XXdata;
            }
        } elsif($f4XXcode =~ /$subparts/) {
            $this_hash .= ' ' . $f4XXdata;
        } elsif($f4XXcode =~ /[7]/) {
            $thesaurus = $f4XXdata;
        }
    }

#    if($#this_subfield_Zs == 2) {
#        uniqueness_diacritics('geographics_facet', $thesaurus, $this_subfield_Zs[1] . ' (' . $this_subfield_Zs[2] . ' : ' . $this_subfield_Zs[0] . ') ');
#    } elsif($#this_subfield_Zs == 1) {
#        uniqueness_diacritics('geographics_facet', $thesaurus, $this_subfield_Zs[1] . ' (' . $this_subfield_Zs[0] . ')');
#    } elsif($#this_subfield_Zs == 0) {
#        uniqueness_diacritics('geographics_facet', $thesaurus, $this_subfield_Zs[0]);
#    } elsif($#this_subfield_Zs > 2 ) {
#        foreach $t ($this_subfield_Zs) {
#            $too_many_geographics .= ' ' . $t;
#        }
#        uniqueness_diacritics('geographics_facet', $thesaurus, $too_many_geographics);
#    }
#    undef(@this_subfield_Zs);

#    uniqueness_diacritics($hash_tag, $thesaurus, $this_hash);

}

foreach $f48X ($record->field('48[015]')) {

    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

}

#    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

    $heading_raw =~ s/[ ]*\.\|/|/g;
    uniqueness_diacritics($hash_tag, "$this_label ", $heading_raw);

} #END: foreach $f4XX
 
foreach $f5XX ($record->field('5..')) {
    $this_tag = $f5XX->tag();
    $indicator1 = $f5XX->indicator(1);
    $indicator2 = $f5XX->indicator(2);

    $this_hash = '';
    $this_label = '';

    $heading_raw = '';

    if($this_tag =~ /500/) {
        $hash_tag = 'authors_personal';
        $codes = '[a-dghj-tvwx-z68]';
        $relators = '[e]';
    } elsif($this_tag =~ /510/) {
        $hash_tag = 'authors_corporate';
        $codes = '[a-df-hk-tvwx-z68]';
        $relators = '[e]';
    } elsif($this_tag =~ /511/) {
        $hash_tag = 'authors_conferences';
        $codes = '[ac-hklnpqstvwx-z68]';
        $relators = '[j]';
    } elsif($this_tag =~ /530/) {
        $hash_tag = 'titles_uniform';
        $codes = '[adf-ik-pr-tvwx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /548/) {
        $hash_tag = 'subjects_chronological';
        $codes = '[aivwx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /550/) {
        $hash_tag = 'subjects_topical';
        $codes = '[abvwx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /551/) {
        $hash_tag = 'subjects_geographic';
        $codes = '[abvwx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    } elsif($this_tag =~ /555/) {
        $hash_tag = 'subjects_genre';
        $codes = '[abvwx-z05]';
        $linkage = '[6]';
        $field_link = '[8]';
    }

    $hash_tag .= '_related';

if($this_tag =~ /500|510|511|548/) {

    foreach $f5XX_subfields ($f5XX->subfields()) {
        ($f5XXcode, $f5XXdata ) = @$f5XX_subfields;

#GET WORK TITLE WORKED IN HERE

    if($f5XXcode !~ /w/) {
        $heading_raw .= '|' . $f5XXcode . $f5XXdata;
    }

        if($f5XXcode =~ /$codes/) {
            if($f5XXcode =~ /a/) {
                $this_hash = $f5XXdata;
            } elsif($f5XXcode =~ /i/) {
                $this_label = $f5XXdata;
            } elsif($f5XXcode =~ /[vxy]/) {
                $this_hash .= '--' . $f5XXdata;
            } elsif($f5XXcode =~ /[z]/) {
#                if($f5XX->subfield('w')) {
#                    if(substr($f5XX->subfield('w'), 0, 1) =~ /a/) {
#                        $this_label = 'Earlier heading: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /b/) {
#                        $this_label = 'Later heading: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /d/) {
#                        $this_label = 'Acronym: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /f/) {
#                        $this_label = 'Musical composition: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /g/) {
#                        $hash_tag =~ s/related/broader/;
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /h/) {
#                        $hash_tag = s/related/narrower/;
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /i/) {
#                        $this_label = ''; #Reference instruction phrase in subfield $i
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /t/) {
#                        $this_label = 'Immediate parent body: ';
#                    } else {
#                        $this_label = '';
#                    }
#
#                    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);
#                }

                $this_hash .= '|z' . $f5XXdata;
                $f5XXdata =~ s/\.$//;
                push(@this_subfield_Zs, $f5XXdata);
            } elsif($f5XXcode =~ /w/) {
                if(substr($f5XXdata, 0, 1) =~ /a/) {
                    $this_label = 'Earlier heading: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /b/) {
                    $this_label = 'Later heading: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /d/) {
                    $this_label = 'Acronym: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /f/) {
                    $this_label = 'Musical composition: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /g/) {
                    $hash_tag =~ s/related/broader/;
                } elsif(substr($f5XXdata, 0, 1) =~ /h/) {
                    $hash_tag = s/related/narrower/;
                } elsif(substr($f5XXdata, 0, 1) =~ /i/) {
                    $this_label = ''; #Reference instruction phrase in subfield $i
                } elsif(substr($f5XXdata, 0, 1) =~ /t/) {
                    $this_label = 'Immediate parent body: ';
                } else {
                    $this_label = '';
                }
            } else {
                $this_hash .= ' ' . $f5XXdata;
            }
        }
    }

#    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

}

if($this_tag =~ /530/) {

    foreach $f5XX_subfields ($f5XX->subfields()) {
    ($f5XXcode, $f5XXdata ) = @$f5XX_subfields;

    if($f5XXcode !~ /w/) {
        $heading_raw .= '|' . $f5XXcode . $f5XXdata;
    }

        if($f5XXcode =~ /$codes/) {
            if($f5XXcode =~ /a/) {
                $this_hash = '\t' . $f5XXdata;
            } elsif($f5XXcode =~ /i/) {
                $this_label = $f5XXdata;
            } elsif($f5XXcode =~ /[vxy]/) {
                $this_hash .= '--' . $f5XXdata;
            } elsif($f5XXcode =~ /[z]/) {
#                if($f5XX->subfield('w')) {
#                    if(substr($f5XX->subfield('w'), 0, 1) =~ /a/) {
#                        $this_label = 'Earlier heading: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /b/) {
#                        $this_label = 'Later heading: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /d/) {
#                        $this_label = 'Acronym: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /f/) {
#                        $this_label = 'Musical composition: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /g/) {
#                        $hash_tag =~ s/related/broader/;
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /h/) {
#                        $hash_tag = s/related/narrower/;
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /i/) {
#                        $this_label = ''; #Reference instruction phrase in subfield $i
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /t/) {
#                        $this_label = 'Immediate parent body: ';
#                    } else {
#                        $this_label = '';
#                    }
#
#                    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);
#                }

                $this_hash .= '|z' . $f5XXdata;
                $f5XXdata =~ s/\.$//;
                push(@this_subfield_Zs, $f5XXdata);
            } elsif($f5XXcode =~ /w/) {
                if(substr($f5XXdata, 0, 1) =~ /a/) {
                    $this_label = 'Earlier heading: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /b/) {
                    $this_label = 'Later heading: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /d/) {
                    $this_label = 'Acronym: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /f/) {
                    $this_label = 'Musical composition: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /g/) {
                    $hash_tag =~ s/related/broader/;
                } elsif(substr($f5XXdata, 0, 1) =~ /h/) {
                    $hash_tag = s/related/narrower/;
                } elsif(substr($f5XXdata, 0, 1) =~ /i/) {
                    $this_label = ''; #Reference instruction phrase in subfield $i
                } elsif(substr($f5XXdata, 0, 1) =~ /t/) {
                    $this_label = 'Immediate parent body: ';
                } else {
                    $this_label = '';
                }
            } else {
                $this_hash .= ' ' . $f5XXdata;
            }
        }
    }
   
#    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

}

if($this_tag =~ /55[015]/) {

    foreach $f5XX_subfields ($f5XX->subfields()) {
        ($f5XXcode, $f5XXdata ) = @$f5XX_subfields;

    if($f5XXcode !~ /w/) {
        $heading_raw .= '|' . $f5XXcode . $f5XXdata;
    }

        if($f5XXcode =~ /$codes/) {
            if($f5XXcode =~ /[a]/) {
                $this_hash = $f5XXdata;
                $f5XXdata =~ s/\.$//;

                if($this_tag =~ /550/) {
                    if($f5XXdata =~ /^($cardinals_keys)(?: |-)($cardinals_keys)(?: |-)($cardinals_keys)(?: $|$|, A.D)/i) {
                        $hash_tag = 'subjects_chronological_related';
                    } elsif($f5XXdata =~ /^($cardinals_keys)( |-o-|-)($cardinals_keys)(?: $|$|, A.D)/i) {
                        $hash_tag = 'subjects_chronological_related';
                    } elsif($f5XXdata =~ /^($cardinals_keys)(?: |-)($places_keys)(?: $|$|, A.D)/i) {
                        $hash_tag = 'subjects_chronological_related';
                    } elsif($f5XXdata =~ /^($cardinals_keys)(?: |-)($decades_keys)/i) {
                        $hash_tag = 'subjects_chronological_related';
                    } elsif($f5XXdata =~ /^($cardinals_keys)(?: |-)($ordinals_keys) century/i) {
                        $hash_tag = 'subjects_chronological_related';
                    } elsif($f5XXdata =~ /^($ordinals_keys) century/i) {
                        $hash_tag = 'subjects_chronological_related';
                    }
                }
            } elsif($f5XXcode =~ /i/) {
                $this_label = $f5XXdata;            
            } elsif($f5XXcode =~ /[vxy]/) {
                $this_hash .= '--' . $f5XXdata;
            } elsif($f5XXcode =~ /[z]/) {
#                if($f5XX->subfield('w')) {
#                    if(substr($f5XX->subfield('w'), 0, 1) =~ /a/) {
#                        $this_label = 'Earlier heading: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /b/) {
#                        $this_label = 'Later heading: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /d/) {
#                        $this_label = 'Acronym: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /f/) {
#                        $this_label = 'Musical composition: ';
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /g/) {
#                        $hash_tag =~ s/related/broader/;
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /h/) {
#                        $hash_tag = s/related/narrower/;
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /i/) {
#                        $this_label = ''; #Reference instruction phrase in subfield $i
#                    } elsif(substr($f5XX->subfield('w'), 0, 1) =~ /t/) {
#                        $this_label = 'Immediate parent body: ';
#                    } else {
#                        $this_label = '';
#                    }
#
#                    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);
#                }
                $this_hash .= '|z' . $f5XXdata;
                $f5XXdata =~ s/\.$//;
                push(@this_subfield_Zs, $f5XXdata);
            } elsif($f5XXcode =~ /w/) {
                if(substr($f5XXdata, 0, 1) =~ /a/) {
                    $this_label = 'Earlier heading: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /b/) {
                    $this_label = 'Later heading: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /d/) {
                    $this_label = 'Acronym: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /f/) {
                    $this_label = 'Musical composition: ';
                } elsif(substr($f5XXdata, 0, 1) =~ /g/) {
                    $hash_tag =~ s/related/broader/;
                } elsif(substr($f5XXdata, 0, 1) =~ /h/) {
                    $hash_tag = s/related/narrower/;
                } elsif(substr($f5XXdata, 0, 1) =~ /i/) {
                    $this_label = ''; #Reference instruction phrase in subfield $i
                } elsif(substr($f5XXdata, 0, 1) =~ /t/) {
                    $this_label = 'Immediate parent body: ';
                } else {
                    $this_label = '';
                }
            } else {
                $this_hash .= ' ' . $f5XXdata;
            }
        } elsif($f5XXcode =~ /$subparts/) {
            $this_hash .= ' ' . $f5XXdata;
        } elsif($f5XXcode =~ /[7]/) {
            $thesaurus = $f5XXdata;
        }
    }

#    if($#this_subfield_Zs == 2) {
#        uniqueness_diacritics('geographics_facet', $thesaurus, $this_subfield_Zs[1] . ' (' . $this_subfield_Zs[2] . ' : ' . $this_subfield_Zs[0] . ') ');
#    } elsif($#this_subfield_Zs == 1) {
#        uniqueness_diacritics('geographics_facet', $thesaurus, $this_subfield_Zs[1] . ' (' . $this_subfield_Zs[0] . ')');
#    } elsif($#this_subfield_Zs == 0) {
#        uniqueness_diacritics('geographics_facet', $thesaurus, $this_subfield_Zs[0]);
#    } elsif($#this_subfield_Zs > 2 ) {
#        foreach $t ($this_subfield_Zs) {
#            $too_many_geographics .= ' ' . $t;
#        }
#        uniqueness_diacritics('geographics_facet', $thesaurus, $too_many_geographics);
#    }
#    undef(@this_subfield_Zs);

#    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

}

foreach $f58X ($record->field('58[015]')) {

    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

}

#    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

    $heading_raw =~ s/[ ]*\.\|/|/g;
    uniqueness_diacritics($hash_tag, "$this_label ", $heading_raw);

} #END: foreach $f5XX

foreach $f64X ($record->field('64.')) {
    $this_tag = $f64X->tag();
    $indicator1 = $f64X->indicator(1);
    $indicator2 = $f64X->indicator(2);

    $this_label = '';
    $this_hash = '';
    $hash_tag = 'notes_nonpublic';

    if($this_tag =~ /640]/) {
        $this_label = 'Series Dates of Publication, Sequential Designation';
        $codes = '[az68]';
    } elsif($this_tag =~ /641/) {
        $this_label = 'Series Numbering Peculiarities';
        $codes = '[az68]';
    } elsif($this_tag =~ /642/) {
        $this_label = 'Series Numbering Example';
        $codes = '[ad568]';
    } elsif($this_tag =~ /643/) {
        $this_label = 'Series Place and Publisher';
        $codes = '[abd68]';
    } elsif($this_tag =~ /644/) {
        $this_label = 'Series Analysis Practice';
        $codes = '[abd568]';
    } elsif($this_tag =~ /645/) {
        $this_label = 'Series Tracing Practice';
        $codes = '[ad568]';
    } elsif($this_tag =~ /646/) {
        $this_label = 'Series Classification Practice';
        $codes = '[ad568]';
    }

    foreach $f64X_subfields ($f64X->subfields()) {
        ($f64Xcode, $f64Xdata ) = @$f64X_subfields;

        if($f64Xcode =~ /$codes/) {
            if($f64Xcode =~ /a/) {
                $this_hash .= $f64Xdata . ' ';
                if($this_tag =~ /644/) {
                    if($f64Xdata =~ /f/) {
                        $this_hash = 'Analyzed in full';
                    } elsif($f64Xdata =~ /p/) {
                        $this_hash = 'Analyzed in part';
                    } elsif($f64Xdata =~ /n/) {
                        $this_hash = 'Not analyzed';
                    }
                } elsif($this_tag =~ /645/) {
                    if($f64Xdata =~ /t/) {
                        $this_hash = 'Traced as a series added entry';
                    } elsif($f64Xdata =~ /n/) {
                        $this_hash = 'Not traced as a series added entry';
                    }
                } elsif($this_tag =~ /646/) {
                    if($f64Xdata =~ /s/) {
                        $this_hash = 'Volumes are classified separately';
                    } elsif($f64Xdata =~ /c/) {
                        $this_hash = 'Volumes are classified as a collection';
                    } elsif($f64Xdata =~ /m/) {
                        $this_hash = 'Volumes are classified with main or other series';
                    }
                }

            } elsif($f64Xcode =~ /[b]/) {
                $this_hash .= ': ' . $f64Xdata;
            } elsif($f64Xcode =~ /[d]/) {
                $this_hash .= '; ' . $f64Xdata;
            } elsif($f64Xcode =~ /[z]/) {
                $this_hash .= ' -- ' . $f64Xdata;
            }
        }
    }

    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

} #END: foreach $f64X

foreach $f66X ($record->field('6[6-8].')) {
    $this_tag = $f66X->tag();
    $indicator1 = $f66X->indicator(1);
    $indicator2 = $f66X->indicator(2);

    $this_label = '';
    $this_hash = '';

    if($this_tag =~ /66[34]/) {
        $hash_tag = 'notes_public';
        $codes = '[abt68]';
    } elsif($this_tag =~ /66[56]/) {
        $hash_tag = 'notes_public';
        $codes = '[a68]';
    } elsif($this_tag =~ /667/) {
        $hash_tag = 'notes_nonpublic';
        $codes = '[a568]';
    } elsif($this_tag =~ /670/) {
        $hash_tag = 'source_data';
        $codes = '[abu68]';
    } elsif($this_tag =~ /675/) {
        $hash_tag = 'source_nondata';
        $codes = '[a68]';
    } elsif($this_tag =~ /678/) {
        $hash_tag = 'notes_public';
        $codes = '[abu68]';
    } elsif($this_tag =~ /680/) {
        $hash_tag = 'notes_public';
        $codes = '[ai568]';
    } elsif($this_tag =~ /681/) {
        $hash_tag = 'notes_nonpublic';
        $codes = '[ai68]';
    } elsif($this_tag =~ /682/) {
        $hash_tag = 'notes_nonpublic';
        $codes = '[ai068]';
    } elsif($this_tag =~ /688/) {
        $hash_tag = 'notes_public';
        $codes = '[a568]';
    }

    foreach $f66X_subfields ($f66X->subfields()) {
        ($f66Xcode, $f66Xdata ) = @$f66X_subfields;

        if($f66Xcode =~ /$codes/) {
            if($f66Xcode =~ /a/) {
                $this_hash .= $f66Xdata;
            } elsif($f66Xcode =~ /b/) {
                $this_hash .= ' ' . $f66Xdata;
            } elsif($f66Xcode =~ /i/) {
                $this_label .= $f66Xdata . ' ';
            }
        }
    }

    uniqueness_diacritics($hash_tag, "$this_label ", $this_hash);

} #END: foreach $f66X

#foreach $f856 ($record->field('856|956')) {
#    $indicator1 = $f856->indicator(1);
#    $indicator2 = $f856->indicator(2);
#    $this_tag = $f856->tag;
#    $this_label = '';
#    $this_hash = 'Connect to resource online. ';
#
##    if($indicator1 =~ /0/) {
##        $this_hash = 'Access method: E-mail. ';
##    } elsif($indicator1 =~ /1/) {
##        $this_hash = 'Access method: FTP. ';
##    } elsif($indicator1 =~ /2/) {
##        $this_hash = 'Access method: Remote login. ';
##    } elsif($indicator1 =~ /3/) {
##        $this_hash = 'Access method: Dial-up. ';
##    } elsif($indicator1 =~ /4/) {
##        $this_hash = 'Access method: HTTP. ';
##    } elsif(($indicator1 =~ /7/) && ($f856->subfield('2'))){
##        $this_hash = 'Access method: ' . $f856->subfield('2') . '. ';
##    }
#
#    if($indicator2 =~ /1/) {
#        $this_hash .= 'Version of resource. ';
#    } elsif($indicator2 =~ /2/) {
#        $this_hash .= 'Related resource. ';
#    }
#
#    foreach $f856_subfields ($f856->subfields()) {
#    ($f856code, $f856data ) = @$f856_subfields;
#            if($f856code =~ /[a-dfh-tv-z3]/) {
#                $this_hash .= $f856data;
#            } elsif($f856code =~ /[u]/) {
#                $this_label .= $f856data;
#            }
#    }
#
#    uniqueness_diacritics('urls', "$this_label ", $this_hash);
#}

foreach $f880 ($record->field('880')) {
    #Alternate Graphic Representation
}

foreach $f907 ($record->field('907')) {
    foreach $f907_subfields ($f907->subfields()) {
    ($f907code, $f907data ) = @$f907_subfields;
            if($f907code =~ /a/) {
                $query_insert{'local_control_number'} = $institution . substr($f907data, 1, 8);
            } elsif($f907code =~ /b/) {
                $query_insert{'updated_date'} = $f907data;
            } elsif($f907code =~ /c/) {
                $query_insert{'created_date'} = $f907data;
            }
    }

}

foreach $f998 ($record->field('998')) {
    foreach $f998_subfields ($f998->subfields()) {
    ($f998code, $f998data ) = @$f998_subfields;
            if($f998code =~ /a/) {
                uniqueness('bloc', '', $institution . '\t' . $f998data);
            } elsif($f998code =~ /b/) {
                $query_insert{'cat_date'} = $f998data;
            } elsif($f998code =~ /e/) {
                $query_insert{'auth_status'} = $f998data;
            }
    }
}

    foreach $date_field( cat_date, updated_date, created_date ) {
        if(substr($query_insert{$date_field}, 6, 1) >= 8) {
        $prepend = '19';
        } else {
        $prepend = '20';
        }
        if($query_insert{$date_field} =~ /([0-9]{2})-([0-9]{2})-([0-9]{2})/) {
            $query_insert{$date_field} = $prepend . $3 . '-' . $1 . '-' . $2;
        }
    }


####START: PRINT VALUES TO BATCH FILES

@keys = keys %query_insert;
@values = values %query_insert;

$mysql_keys = join(", ", @keys);
$mysql_values = join("\t", @values);
$mysql_values =~ s/\\\t/\\ \t/g;

print $batch_loader $mysql_values . "\n";

####END: PRINT VALUES TO BATCH FILES
                undef($mysql_values);
                undef(%query_insert);
                undef(@keys);
                undef(@values);

}

}

    close(IN) || "cannot close $next_file: $!";

unlink($next_file);

        $batch_loader->autoflush(1);
        close($batch_loader, 2) || "cannot close $batch_loader: $!";

        $tracings_batch_loader->autoflush(1);
        close($tracings_batch_loader, 2) || "cannot close $tracings_batch_loader: $!";
}



$authorities_keys_textfiles = $baseurl . '/' . $folder_name . '_textfiles/authorities.key';

$authorities_keys_loader = IO::File->new("> $authorities_keys_textfiles") || die "cannot open $authorities_keys_textfiles for writing: $!\n";
binmode( $authorities_keys_loader, ':utf8' );
flock($authorities_keys_loader, 2);

    print $authorities_keys_loader $mysql_keys . "\n";

        $authorities_keys_loader->autoflush(1);
        close($authorities_keys_loader, 2) || "cannot close $authorities_keys_loader: $!";


@current_time = localtime;

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { mysql_enable_utf8 => 1 }) or die "couldn't connect to database: " . DBI->errstr;

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_of_file_creation_time = "$HOUR:$MIN:$SEC";

if ($ARGV[0] =~ /CREATING/) {
    $sth1= $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'authorities_record_table'}");
    $this_table = $db_local_tables_hash{'authorities_record_table'};
} else {
    $sth1= $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'authorities_record_temp_table'}");
    $this_table = $db_local_tables_hash{'authorities_record_temp_table'};
}

$where = $baseurl . '/' . $folder_name . '_textfiles';

#####START: INITIAL TABLE LOAD

while (defined($next_file = <$where/*.out>)) {

    $sth2 = $dbh->do("LOAD DATA INFILE '" . $next_file . "' REPLACE INTO TABLE $this_table FIELDS TERMINATED BY '\t' (" . $mysql_keys . ")") || print "intial load died in local\n";

unlink($next_file);

}

unlink($authorities_keys_textfiles);

#####END: INITIAL TABLE LOAD


#######START: UPDATE ACTUAL AUTH TABLE WITH DATA IN TEMP TABLE

if ($ARGV[0] ne 'CREATING') {

$this_table = $db_local_tables_hash{'authorities_record_table'};
$that_table = $db_local_tables_hash{'authorities_record_temp_table'};

    $querystring = "SHOW FIELDS FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

    while ( @row = $query->fetchrow_array ) {
        push (@keys, $row[0]); ## field name
    }

$query->finish();

pop(@keys);

@update_strings = ();

    foreach $field_name (@keys) {
        push(@update_strings, $this_table . '.' . $field_name . ' = ' . $that_table . '.' . $field_name);
    }
    
    $temp_updates = join(", ", @update_strings);

        $querystring = "UPDATE $this_table, $that_table
            SET $temp_updates
            WHERE $this_table.local_control_number = $that_table.local_control_number";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

        $query->finish();

        $querystring = "DELETE $that_table.*
            FROM $that_table
            LEFT JOIN $this_table
            ON $that_table.local_control_number = $this_table.local_control_number
            WHERE $that_table.local_control_number = $this_table.local_control_number";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();

        $querystring = "INSERT $this_table
            ($mysql_keys)
            SELECT $mysql_keys from $that_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();

}

#######END: UPDATE ACTUAL AUTH TABLE WITH DATA IN TEMP TABLE

#######START: CREATING FILES FOR LOAD INTO LOCAL HOLDINGS TABLE AND CONSORTIUM AUTHS AND HOLDINGS TABLES

if ($consortium ne '') {

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_external_update_time = "$HOUR:$MIN:$SEC";

}

$last_update_time = ($current_time[5] + 1900) . "-" . ($current_time[4] + 1) . "-$current_time[3] $current_time[2]:$current_time[1]:$current_time[0]";

#print "last_update_time: " . $last_update_time . "\n";
#
#$this_table = $db_local_tables_hash{'holdings_record_table'};
#$recordcount = 0;
#
#$position = 0;
#
#    $querystring = "SHOW FIELDS FROM $this_table";
#
#        $query = $dbh->prepare($querystring)
#            or die("couldn't prepare statement: ". $dbh->errstr);
#
#        $query->execute()
#            or die("couldn't execute statement: ". $dbh->errstr);
#
#    while ( @row = $query->fetchrow_array ) {
#        push (@holdings_keys, $row[0]); ## field name
#    }
#
#$query->finish();
#
#pop(@holdings_keys);

$this_table = $db_local_tables_hash{'authorities_record_table'};

    $querystring = "SHOW FIELDS FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

    %authorities2holdings_hash = ();

    while ( @row = $query->fetchrow_array ) {
        push (@fields_array, $row[0]); ## field name
           $authorities2holdings_hash{$row[0]} = $position;
        $position++;
    }

        $query->finish();

pop(@fields_array);

    $fields_array_string = join(", ", @fields_array);
#    $mysql_holdings_keys = join(", ", @holdings_keys);
#
#$holdings_keys_textfiles = $baseurl . '/holdings_textfiles/holdings.key';
#
#$holdings_keys_loader = IO::File->new("> $holdings_keys_textfiles") || die "cannot open $holdings_keys_textfiles for writing: $!\n";
#binmode( $holdings_keys_loader, ':utf8' );
#flock($holdings_keys_loader, 2);
#
#    print $holdings_keys_loader $mysql_holdings_keys . "\n";
#
#        $holdings_keys_loader->autoflush(1);
#        close($holdings_keys_loader, 2) || "cannot close $holdings_keys_loader: $!";

if ($consortium ne '') {

#$consortium_holdings_keys_textfiles = $baseurl . '/consortium_holdings_textfiles/consortiumholdings.key';
$consortium_authorities_keys_textfiles = $baseurl . '/consortium_authorities_textfiles/consortiumauthorities.key';

#$consortium_holdings_keys_loader = IO::File->new("> $consortium_holdings_keys_textfiles") || die "cannot open $consortium_holdings_keys_textfiles for writing: $!\n";
#binmode( $consortium_holdings_keys_loader, ':utf8' );
#flock($consortium_holdings_keys_loader, 2);

$consortium_authorities_keys_loader = IO::File->new("> $consortium_authorities_keys_textfiles") || die "cannot open $consortium_authorities_keys_textfiles for writing: $!\n";
binmode( $consortium_authorities_keys_loader, ':utf8' );
flock($consortium_authorities_keys_loader, 2);

#    print $consortium_holdings_keys_loader $mysql_holdings_keys . "\n";
    print $consortium_authorities_keys_loader $fields_array_string . "\n";

#        $consortium_holdings_keys_loader->autoflush(1);
#        close($consortium_holdings_keys_loader, 2) || "cannot close $consortium_holdings_keys_loader: $!";

        $consortium_authorities_keys_loader->autoflush(1);
        close($consortium_authorities_keys_loader, 2) || "cannot close $consortium_authorities_keys_loader: $!";
}

if ($ARGV[0] eq 'CREATING') {
    $IN_string = "'n', 's', 'z', 'x'";
} else {
    $IN_string = "'n', 's', 'z'";
}

$limit_start = 0;
$rownumber = 1;
while($rownumber > 0) {

        $querystring = "SELECT * from $this_table
            WHERE solr_update >= ? 
            AND auth_status NOT IN ($IN_string)
            LIMIT ?, 10000";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute($last_update_time, $limit_start)
            or die("couldn't execute statement: ". $dbh->errstr);

$limit_start += 10000;
$rownumber = $query->rows();

    print "rownumber: " . $rownumber . "\n";

while(@query_array = $query->fetchrow_array()) {
    $int = $recordcount / 10000;

    if ($int =~ m/^\d+$/) {

#        if($recordcount > 0) {
#        $holdings_batch_loader->autoflush(1);
#        close($holdings_batch_loader, 2) || "cannot close $holdings_batch_loader: $!";
#        }

#$holdings_batch_textfiles = $baseurl . '/holdings_textfiles/holdings' . $recordcount .  '.txt';
#
#$holdings_batch_loader = IO::File->new("> $holdings_batch_textfiles") || die "cannot open $holdings_batch_textfiles for writing: $!\n";
#binmode( $holdings_batch_loader, ':utf8' );
#flock($holdings_batch_loader, 2);


if ($consortium ne '') {

        if($recordcount > 0) {

#        $consortium_holdings_batch_loader->autoflush(1);
#        close($consortium_holdings_batch_loader, 2) || "cannot close $consortium_holdings_batch_loader: $!";

        $consortium_authorities_batch_loader->autoflush(1);
        close($consortium_authorities_batch_loader, 2) || "cannot close $consortium_authorities_batch_loader: $!";
        }

#$consortium_holdings_batch_textfiles = $baseurl . '/consortium_holdings_textfiles/consortium_from_1' . $folder_name . '_' . $recordcount .  '.txt';
$consortium_authorities_batch_textfiles = $baseurl . '/consortium_authorities_textfiles/consortium_from_1' . $folder_name . '_' . $recordcount .  '.txt';

#$consortium_holdings_batch_loader = IO::File->new("> $consortium_holdings_batch_textfiles") || die "cannot open $consortium_holdings_batch_textfiles for writing: $!\n";
#binmode( $consortium_holdings_batch_loader, ':utf8' );
#flock($consortium_holdings_batch_loader, 2);

$consortium_authorities_batch_loader = IO::File->new("> $consortium_authorities_batch_textfiles") || die "cannot open $consortium_authorities_batch_textfiles for writing: $!\n";
binmode( $consortium_authorities_batch_loader, ':utf8' );
flock($consortium_authorities_batch_loader, 2);
}

    }

$recordcount++;

#pop(@query_array);
#
#foreach(@query_array) {
#    s/\t/\\t/g;
#}
#
#@blocs = split("\n", $query_array[$authorities2holdings_hash{'bloc'}]);
#
#foreach $this_bloc (@blocs) {
#
#        @holdings_array = ();
#    foreach $this_holding_key(@holdings_keys) {
#
#        if($this_holding_key =~ /lib_has|latest_received/) {
#            push(@holdings_array, '');
#        } elsif($this_holding_key =~ /hloc/) {
#            push(@holdings_array, $this_bloc);
#        } elsif($this_holding_key =~ /holding_status/) {
#            push(@holdings_array, $query_array[$authorities2holdings_hash{'auth_status'}]);
#        } else {
#            push(@holdings_array, $query_array[$authorities2holdings_hash{$this_holding_key}]);
#        }
#
#
#    }
#
#        $query_array_string = join("\t", @query_array);
#        $query_array_string =~ s/\\\t/\\ \t/g;
#        $query_array_string =~ s/\n/\\n/g;
#
#        $holdings_array_string = join("\t", @holdings_array);
#        $holdings_array_string =~ s/\\\t/\\ \t/g;
#        $holdings_array_string =~ s/\n/\\n/g;
#
#        print $holdings_batch_loader $holdings_array_string . "\n";
#
#if ($consortium ne '') {
#
#        print $consortium_holdings_batch_loader $holdings_array_string . "\n";
#        print $consortium_authorities_batch_loader $query_array_string . "\n";
#}
#
#        $line_count++;
#}

    }

}

$query->finish();

#if($recordcount > 0) {
#
#        $mysql_holdings_keys = join(", ", @holdings_keys);
#
#        $holdings_batch_loader->autoflush(1);
#        close($holdings_batch_loader, 2) || "cannot close $holdings_batch_loader: $!";
#
#if ($consortium ne '') {
#
#        $consortium_holdings_batch_loader->autoflush(1);
#        close($consortium_holdings_batch_loader, 2) || "cannot close $consortium_holdings_batch_loader: $!";
#
#        $consortium_authorities_batch_loader->autoflush(1);
#        close($consortium_authorities_batch_loader, 2) || "cannot close $consortium_authorities_batch_loader: $!";
#}
#
#########END: CREATING FILES FOR LOAD INTO LOCAL HOLDINGS TABLE AND CONSORTIUM AUTHS AND HOLDINGS TABLES
#
##########START: LOADING FILES INTO LOCAL HOLDINGS TABLE
#
#    $where = $baseurl . '/holdings_textfiles';
#
#if ($ARGV[0] =~ /CREATING/) {
#    $dbh->do("ALTER TABLE " . $db_local_tables_hash{'holdings_record_table'} . " DISABLE KEYS");
#}
#
#while (defined($next_file = <$where/*.txt>)) {
#
#   $sth3 = $dbh->do("LOAD DATA INFILE '" . $next_file . "' REPLACE INTO TABLE $db_local_tables_hash{'holdings_record_table'} FIELDS TERMINATED BY '\t' (" . $mysql_holdings_keys . ")") || print "died again sth3 local\n";
#
##unlink($next_file);
#
#}
#
#if ($ARGV[0] =~ /CREATING/) {
#    $dbh->do("ALTER TABLE " . $db_local_tables_hash{'holdings_record_table'} . " ENABLE KEYS");
#}
#
##########END: LOADING FILES INTO LOCAL HOLDINGS TABLE
#
#
#}

if ($consortium ne '') {

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_external_update_time = "$HOUR:$MIN:$SEC";

}

$dbh->disconnect;

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_time = "$HOUR:$MIN:$SEC";

print "Start time: " . $start_time . "\nEnd of file creation time: " . $end_of_file_creation_time . "\nEnd time: " . $end_time . "\n Number of files: " . $total_number_of_files . "\n";
print "start_external_update_time: " . $start_external_update_time . "\n";
print "end_external_update_time: " . $end_external_update_time . "\n";

close (STDERR) || "cannot close unicode_errors.txt: $!";
close (STDOUT) || "cannot close unicode_prints.txt: $!";

#SUBROUTINES

sub hashes_and_arrays {

$hash_files = $baseurl . '/hashfiles';

while (defined($next_hash_file = <$hash_files/*.pl>)) {
require $next_hash_file;
}

@months = ("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");

%cardinals = ("one" => "1", "two" => "2", "three" => "3", "four" => "4", "five" => "5", "six" => "6", "seven" => "7", "eight" => "8", "nine" => "9", "ten" => "10", "eleven" => "11", "twelve" => "12", "thirteen" => "13", "fourteen" => "14", "fifteen" => "15", "sixteen" => "16", "seventeen" => "17", "eighteen" => "18", "nineteen" => "19", "twenty" => "20", "thirty" => "30", "forty" => "40", "fifty" => "50", "sixty" => "60", "seventy" => "70", "eighty" => "80", "ninety" => "90");
@cardinalskeys = keys %cardinals;
$cardinals_keys = join("|", @cardinalskeys);

%ordinals = ("first" => "1", "second" => "2", "third" => "3", "fourth" => "4", "fifth" => "5", "sixth" => "6", "seventh" => "7", "eighth" => "8", "ninth" => "9", "tenth" => "10", "eleventh" => "11", "twelfth" => "12", "thirteenth" => "13", "fourteenth" => "14", "fifteenth" => "15", "sixteenth" => "16", "seventeenth" => "17", "eighteenth" => "18", "nineteenth" => "19", "twentieth" => "20", "thirtieth" => "30", "fortieth" => "40", "fiftieth" => "50", "sixtieth" => "60", "seventieth" => "70", "eightieth" => "80", "ninetieth" => "90");
@ordinalskeys = keys %ordinals;
$ordinals_keys = join("|", @ordinalskeys);

%decades = ("tens" => "10", "twenties" => "20", "thirties" => "30", "forties" => "40", "fifties" => "50", "sixties" => "60", "seventies" => "70", "eighties" => "80", "nineties" => "90", "hundreds" => "00");
@decadeskeys = keys %decades;
$decades_keys = join("|", @decadeskeys);

%places = ("hundred" => "100", "thousand" => "1000");
@placeskeys = keys %places;
$places_keys = join("|", @placeskeys);

%alphanumarray = ("0" => "00", "1" => "01", "2" => "02", "3" => "03", "4" => "04", "5" => "05", "6" => "06", "7" => "07", "8" => "08", "9" => "09", "A" => "10", "B" => "11", "C" => "12", "D" => "13", "E" => "14", "F" => "15", "G" => "16", "H" => "17", "I" => "18", "J" => "19", "K" => "20", "L" => "21", "M" => "22", "N" => "23", "O" => "24", "P" => "25", "Q" => "26", "R" => "27", "S" => "28", "T" => "29", "U" => "30", "V" => "31", "W" => "32", "X" => "33", "Y" => "34", "Z" => "35");

%superscripts = ("0" => "\x{2070}", "1" => "\xB9", "2" => "\xB2", "3" => "\xB3", "4" => "\x{2074}", "5" => "\x{2075}", "6" => "\x{2076}", "7" => "\x{2077}", "8" => "\x{2078}", "9" => "\x{2079}", "+" => "\x{207A}", "-" => "\x{207B}", "(" => "\x{207D}", ")" => "\x{207E}");
%subscripts = ("0" => "\x{2080}", "1" => "\x{2081}", "2" => "\x{2082}", "3" => "\x{2083}", "4" => "\x{2084}", "5" => "\x{2085}", "6" => "\x{2086}", "7" => "\x{2087}", "8" => "\x{2088}", "9" => "\x{2089}", "+" => "\x{208A}", "-" => "\x{208B}", "(" => "\x{208D}", ")" => "\x{208E}");
%greekscripts = ("s" => "\x{03c0}", "{" => "\x{03c7}", "\x{DC}" => "\x{03B1}", "\x{DD}" => "\x{03B2}", "[\x{DE}" => "\x{03B3}", "\x{DF}" => "\x{00DF}");
%otherletters = ("\x{BF}" => "\x{00BF}", "\x{C7}" => "\x{00DF}", "\x{DC}" => "[alpha]", "\x{DD}" => "[beta]", "[\x{DE}" => "[gamma]", "\x{DF}" => "\x{00DF}");

if($consortium ne '') {
@MeSH_major_players = keys %MeSH_major_players_hash;
(@other_MeSH_major_players_set = @MeSH_major_players) =~ s/$institution//;
$other_MeSH_major_players_set_string = join("', '", @other_MeSH_major_players_set);

@major_players = keys %major_players_hash;
(@other_major_players_set = @major_players) =~ s/$institution//;
$other_major_players_set_string = join("', '", @other_major_players_set);

@consortium_members = keys %consortium_members_hash;
(@other_consortium_members_set = @consortium_members) =~ s/$institution//;
$other_consortium_members_set_string = join("', '", @other_consortium_members_set);

}

$record_separator = '\\n';

use DBI;

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { mysql_enable_utf8 =>1 }) or die "couldn't connect to database: " . DBI->errstr;

for($k = 10; $k < 36; $k++) {
        $range = $k . "%";
        $l = 0;
#print "k: " . $k . " => range: " . $range . "\n";
        $querystring = "SELECT * FROM $db_local_tables_hash{'lc_classes_table'}
            WHERE range1 LIKE ? ORDER BY range1, range2 DESC";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute($range)
            or die("couldn't execute statement: ". $dbh->errstr);

#print "number of alllcclasses: " . $query->rows . "\n";

        while (@row = $query->fetchrow_array) {
            ($alpha_start = @row[0]) =~ s/ //g;
            ($class_start = @row[1]) =~ s/ //g;
            ($alpha_end = @row[2]) =~ s/ //g;
            ($class_end = @row[3]) =~ s/ //g;
            if(($class_start == 1) && ($class_end =~ /^9999/)) {
                $this_lcc_label = "$alpha_start - @row[4]";
            } else {
                $this_lcc_label = "$alpha_start$class_start - $alpha_end$class_end  @row[4]";
            }

            $range1 = substr(@row[5], 0, 10) . "." . substr(@row[5], 10);
            $range2 = substr(@row[6], 0, 10) . "." . substr(@row[6], 10);
            $range2 =~ s/9999999$/0000000/;
#print "kl: " . $k . "-" . $l . ": " . $this_lcc_label . "--" . $range1 . "-" . $range2 . "\n";

            push @{ $alllcclasses_array[$k][$l]}, "$this_lcc_label", "$range1", "$range2";

            $l++;
        }
}

$query->finish();

$dbh->disconnect;


}

sub uniqueness {
    $label = $_[1];
    ($content = $_[2]) =~ s/\.,[ ]*$|,[ ]*$//g;

    $term = join "", $label, $content;
    
    if($query_insert{$_[0]} !~ /(^|\Q$record_separator\E)\Q$term\E($|\Q$record_separator\E)/) {
        if($query_insert{$_[0]} ne '') {
            $query_insert{$_[0]} .= $record_separator;
        }

        $query_insert{$_[0]} .= $term;
    }
}


sub uniqueness_diacritics {
    $label = $_[1];
    ($content = $_[2]) =~ s/\.[ ]*$|\.,[ ]*$|,[ ]*$//g;

    $term = '';

    if($label eq '') {
    $term = join '', $label, $content;
    } else {
    $label =~ s/ $//;
    $term = join '\\t', $label, $content;
    }

#print $term . "\n";

    while($term =~ /([\xDC-\xDF])/) {
        $term =~ s/$1/$greekscripts{$1}/g;
    }

    if($term =~ /[\xA0-\xFF]/) {
       $term =  marc8_to_utf8($term, 'ignore-errors');
        while($term =~ /([\0]|[\x{FE21}\x{FE23}])/) {
            $term =~ s/$1//g;
        }
    }

    if($term =~ /[\x1b]p([0-9\+\-\(\)]*)[\x1b]s/) {
#print $this_local_control_number . " has superscript characters:" . $term . "\n";
        foreach $power ($term =~ /[\x1b]p([0-9\+\-\(\)]*)[\x1b]s/g) {
            for($j = 0; $j < length($power); $j++) {
                $this_power = substr($power, $j, 1);
                $all_powers .= $superscripts{$this_power};
            }
            $power =~ s/\+/[+]/g;
            $power =~ s/\-/[-]/g;
            $power =~ s/\(/[(]/g;
            $power =~ s/\)/[)]/g;

        $term =~ s/[\x1b]p$power[\x1b]s/$all_powers/g;
#print $term . "\n";
    undef($all_powers);
    }
    }

    if($term =~ /[\x1b]b([0-9\+\-\(\)]*)[\x1b]s/) {
#print $this_local_control_number . " has subscript characters:" . $term . "\n";
        foreach $power ($term =~ /[\x1b]b([0-9\+\-\(\)]*)[\x1b]s/g) {
            for($j = 0; $j < length($power); $j++) {
                $this_power = substr($power, $j, 1);
                $all_powers .= $subscripts{$this_power};
            }
            $power =~ s/\+/[+]/g;
            $power =~ s/\-/[-]/g;
            $power =~ s/\(/[(]/g;
            $power =~ s/\)/[)]/g;

        $term =~ s/[\x1b]b$power[\x1b]s/$all_powers/g;
#print $term . "\n";
    undef($all_powers);
    }
    }

    if($term =~ /[\x1b]g([s{])[\x1b]s/) {
        while($term =~ /[\x1b]g([s{])[\x1b]s/) {
            $power = $1;
            $term =~ s/[\x1b]g$power[\x1b]s/$greekscripts{$power}/g;
        }
    }

    if($term =~ /[\x1b]\(Q[AS]*[ ]*[\x1b]\(B/) {
        foreach $single_quote ($term =~ /([\x1b]\(Q[AS]*[ ]*[\x1b]\(B)/g) {
            print $this_local_control_number . " has escaped single quote characters ($single_quote)in this string: " . $term . "\n";
            $term =~ s/\Q$single_quote\E/'/g;
            print  $term . "\n";
        }
    }

    if($term =~ /\x1a/) {
#        print $this_local_control_number . " has an escape character (either a tab or line ending) in a field marked with \x1a \n";
        $term =~ s/\x1a//g;
    }

    if($term =~ /[\x1b]|[\x1d-\x1f]]|[\x4-\x5]/) {
        foreach $bad_character ($term =~ /([\x1b]|[\x1d-\x1f]]|[\x4-\x5])/g) {
#            print "$this_local_control_number has a bad character ($bad_character) in this string: \n$term\n\n";
                $term =~ s/$bad_character//g;
#            print "$term\n";
        }
    }

    if($term =~ /\\[a-su-zA-SU-Z0-9]/) {
        foreach $bad_escape ($term =~ /\\([a-su-zA-SU-Z0-9])/g) {
#            print "$this_local_control_number has a bad escape character ($bad_escape) in this string: \n$term\n\n";
                $term =~ s/\\$bad_escape/$bad_escape/g;
#            print "$term\n";
        }
    }

        $term = NFC($term);

#print 'term: ' . $term . "\n";

        if($query_insert{$_[0]} !~ /(^|\Q$record_separator\E)\Q$term\E($|\Q$record_separator\E)/) {
            if($query_insert{$_[0]} ne '') {
                $query_insert{$_[0]} .= $record_separator;
            }

#print $term . "\n";

            $query_insert{$_[0]} .= $term;

        }

    }


#sub dates_expansion {
#   $this_tag = $_[0];
#   $thesaurus = $_[1];
#   $date_data = $_[2];
#
#                    uniqueness_diacritics($this_tag, $thesaurus, $date_data);
#
#                    if($date_data =~ /([0-9]{1,2})([a-zA-Z]{2}) century/) {
#                        $this_century1 = ($1 - 1) * 100;
#                        $this_century2 = $this_century1 + 99;
#                        uniqueness($this_tag, $thesaurus . '\\t', "$this_century1-$this_century2");
#                    }
#
#
#                    if($date_data =~ /\bA.D\b|\bTo [0-9]{1,4}$/) {
#
#                        foreach $i ($date_data =~ /\b[0-9]* B.C.-[0-9]* A.D\b|\bTo [0-9]{1,4}/g) {
#                            uniqueness($this_tag, $thesaurus . '\\t', $i);
#                            if($i =~ /([0-9]{1,4}) B.C.-([0-9]{1,4}) A.D/) {
#                                $date1 = $1;
#                                $date2 = $2;
#                            } elsif ($i =~ /To ([0-9]{1,4})/) {
#                                $date1 = 3000;
#                                $date2 = $1;
#                            }
#
#                            uniqueness($this_tag, $thesaurus . '\\t', "$date1 BCE-$date2 CE");
#                            ($decade1 = $date1) =~ s/[0-8]$/9/;
#                            ($decade2 = $date2) =~ s/[0-8]$/9/;
#                            uniqueness($this_tag, $thesaurus . '\\t', "$decade1 BCE-$decade2 CE");
#
#                            if($decade1 > 0) {
#                                $decades_begin = $decade1;
#                                while($decades_begin > 0) {
#                                    ($decades_end = $decades_begin) =~ s/9$/0/;
#                                    $decades_end =~ s/^0$/1/;
#                                    uniqueness($this_tag, $thesaurus . '\\t', "$decades_begin BCE-$decades_end BCE");
#                                    $decades_begin -= 10;
#                                }
#                            }
#
#                            if($decade2 > 1) {
#                                $decades_begin = 1;
#                                while($decades_begin < $decade2) {
#                                    ($decades_end = $decades_begin) =~ s/0$|1$/9/;
#                                    uniqueness($this_tag, $thesaurus . '\\t', "$decades_begin-$decades_end");
#                                    $decades_begin =~ s/^1$/0/;
#                                    $decades_begin += 10;
#                                }
#                            }
#
#                            ($century1 = $decade1) =~ s/[0-8]9$/99/;
#                            ($century2 = $decade2) =~ s/[1-9]0$/00/;
#                            if($century1 > 0) {
#                                $centurys_begin = $century1;
#                                while($centurys_begin > 0) {
#                                    ($centurys_end = $centurys_begin) =~ s/99$/00/;
#                                    $centurys_end =~ s/^00$/1/;
#                                    uniqueness($this_tag, $thesaurus . '\\t', "$centurys_begin BCE-$centurys_end BCE");
#                                    $centurys_begin -= 100;
#                                }
#                            }
#                            if($century2 > 1) {
#                                $centurys_begin = 1;
#                                while($centurys_begin < $century2) {
#                                    ($centurys_end = $centurys_begin) =~ s/00$|1$/99/;
#                                    uniqueness($this_tag, $thesaurus . '\\t', "$centurys_begin-$centurys_end");
#                                    $centurys_begin =~ s/^1$/0/;
#                                    $centurys_begin += 100;
#                                }
#                            }
#
#
#                        }
#
#                    } elsif($date_data =~ /\bB.C\b/) {
#
#                        foreach $i ($date_data =~ /\b[0-9]{1,4} B.C.-[0-9]{1,4} B.C\b|\b[0-9]{1,4}-[0-9]{1,4} B.C\b|\bTo [0-9]{1,4} B.C\b|\b[0-9]{1,4} B.C\b/g) {
#                            uniqueness($this_tag, $thesaurus . '\\t', $i);
#                            if($i =~ /([0-9]{1,4}) B.C.-([0-9]{1,4}) B.C/) {
#                                $date1 = $1;
#                                $date2 = $2;
#                            } elsif ($i =~ /([0-9]{1,4})-([0-9]{1,4}) B.C/) {
#                                $date1 = $1;
#                                $date2 = $2;
#                            } elsif ($i =~ /To ([0-9]{1,4}) B.C/) {
#                                $date1 = 3000;
#                                $date2 = $1;
#                            } elsif ($i =~ /([0-9]{1,4}) B.C/) {
#                                $date1 = $1;
#                                $date2 = $1;
#                            }
#
#                            uniqueness($this_tag, $thesaurus . '\\t', "$date1 BCE-$date2 BCE");
#                            ($decade1 = $date1) =~ s/[0-8]$/9/;
#                            ($decade2 = $date2) =~ s/[1-9]$/0/;
#                            uniqueness($this_tag, $thesaurus . '\\t', "$decade1 BCE-$decade2 BCE");
#                            if($decade1 > $decade2) {
#                                $decades_begin = $decade1;
#                                while($decades_begin > $decade2) {
#                                    ($decades_end = $decades_begin) =~ s/9$/0/;
#                                    $decades_end =~ s/^0$/1/;
#                                    uniqueness($this_tag, $thesaurus . '\\t', "$decades_begin BCE-$decades_end BCE");
#                                    $decades_begin -= 10;
#                                }
#                            }
#
#                            ($century1 = $decade1) =~ s/[0-8]9$/99/;
#                            ($century2 = $decade2) =~ s/[1-9]0$/00/;
#                            uniqueness($this_tag, $thesaurus . '\\t', "$century1 BCE-$century2 BCE");
#                            if($century1 > $century2) {
#                                $centurys_begin = $century1;
#                                while($centurys_begin > $century2) {
#                                    ($centurys_end = $centurys_begin) =~ s/99$/00/;
#                                    $centurys_end =~ s/^00$/1/;
#                                    uniqueness($this_tag, $thesaurus . '\\t', "$centurys_begin BCE-$centurys_end BCE");
#                                    $centurys_begin -= 100;
#                                }
#                            }
#
#                        }
#
#                    } else {
#                        foreach $i ($date_data =~ /\b[0-9]{1,4}-[0-9]{1,4}\b|\b[0-9]{1,4}-|\b[0-9]{1,4}\b/g) {
#                            if($i =~ /([0-9]{1,4})-([0-9]{1,4})/) {
#                                $date1 = $1;
#                                $date2 = $2;
#                            } elsif ($i =~ /([0-9]{1,4})-/) {
#                                $date1 = $1;
#                                $date2 = $query_insert{'original_pubdate_end'};
#                            } else {
#                                $date1 = $i;
#                                $date2 = $i;
#                            }
#
#                            uniqueness($this_tag, $thesaurus . '\\t', "$date1-$date2");
#                            ($decade1 = $date1) =~ s/[1-9]$/0/;
#                            ($decade2 = $date2) =~ s/[0-8]$/9/;
#                            uniqueness($this_tag, $thesaurus . '\\t', "$decade1-$decade2");
#                            if($decade1 < $decade2) {
#                                $decades_begin = $decade1;
#                                while($decades_begin < $decade2) {
#                                    ($decades_end = $decades_begin) =~ s/0$|1$/9/;
#                                    uniqueness($this_tag, $thesaurus . '\\t', "$decades_begin-$decades_end");
#                                    $decades_begin += 10;
#                                }
#                            }
#
#                            ($century1 = $decade1) =~ s/[1-9]0$/00/;
#                            ($century2 = $decade2) =~ s/[0-8]9$/99/;
#                            uniqueness($this_tag, $thesaurus . '\\t', "$century1-$century2");
#                            if($century1 < $century2) {
#                                $centurys_begin = $century1;
#                                while($centurys_begin < $century2) {
#                                    ($centurys_end = $centurys_begin) =~ s/00$|1$/99/;
#                                    uniqueness($this_tag, $thesaurus . '\\t', "$centurys_begin-$centurys_end");
#                                    $centurys_begin += 100;
#                                }
#                            }
#
#                        }
#
#                    }
#
#
#}

